package com.qyer.commons.service.service;/**
 * User: Z J Wu Date: 2016/03/07 Time: 18:27 Package: com.qyer.commons.service.service
 */

import static com.qyer.commons.place.QyerPlace.createChinaProvince;
import static com.qyer.commons.place.QyerPlace.createCity;
import static com.qyer.commons.place.QyerPlace.createCityWithEnglishPY;
import static com.qyer.commons.place.QyerPlace.createContinentWithEnglishPY;
import static com.qyer.commons.place.QyerPlace.createCountryWithEnglishPY;
import static com.qyer.commons.utils.DBUtils.closeAllQuietly;
import static com.qyer.commons.utils.DBUtils.tryConnect;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.qyer.commons.exception.DBException;
import com.qyer.commons.place.City;
import com.qyer.commons.place.Continent;
import com.qyer.commons.place.Country;
import com.qyer.commons.place.Province;
import com.qyer.commons.place.QyerPlace;
import com.qyer.commons.service.db.DMDBResourceManager;
import com.qyer.commons.service.db.QyerDBResourceManager;
import com.qyer.commons.service.model.PlaceDict;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class PlaceService {

  private static final Logger LOGGER = LoggerFactory.getLogger(PlaceService.class);

  private static class InnerHolder {

    private static final PlaceService INSTANCE = new PlaceService();
  }

  private PlaceService() {
    this.qyerDBResourceManager = QyerDBResourceManager.getInstance();
    this.dmdbResourceManager = DMDBResourceManager.getInstance();
  }

  private QyerDBResourceManager qyerDBResourceManager;
  private DMDBResourceManager dmdbResourceManager;

  public static PlaceService getInstance() {
    return InnerHolder.INSTANCE;
  }

  /**
   * 获取所有大洲列表
   *
   * @return
   * @throws DBException
   */
  public List<Continent> loadContinent() throws DBException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "SELECT t.id,t.parentid,t.catename,t.catename_en,t.catename_py FROM " + "qyer" +
      ".category AS t WHERE t.parentid = 1 ORDER BY id";
    List<Continent> continents = Lists.newArrayListWithCapacity(7);
    try {
      conn = tryConnect(qyerDBResourceManager.getDataSource(), 20, "loadContinent");
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        continents.add(createContinentWithEnglishPY(rs.getInt("id"), QyerPlace.EARTH.getId(),
                                                    rs.getString("catename"),
                                                    rs.getString("catename_en"),
                                                    rs.getString("catename_py")));
      }
    } catch (Exception e) {
      throw new DBException(e);
    } finally {
      closeAllQuietly(conn, pstmt, rs);
    }
    return continents;
  }

  /**
   * 根据指定sql, 加载国家列表
   *
   * @param sql
   * @return
   * @throws DBException
   */
  private List<Country> loadCountries(String sql) throws DBException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    List<Country> countries = Lists.newArrayListWithCapacity(7);
    try {
      conn = tryConnect(qyerDBResourceManager.getDataSource(), 20, "loadCounties");
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        countries.add(createCountryWithEnglishPY(rs.getInt("id"), rs.getInt("parentid"),
                                                 rs.getString("catename"),
                                                 rs.getString("catename_en"),
                                                 rs.getString("catename_py")));
      }
    } catch (Exception e) {
      throw new DBException(e);
    } finally {
      closeAllQuietly(conn, pstmt, rs);
    }
    return countries;
  }

  /**
   * 获取所有国家列表
   *
   * @return
   * @throws DBException
   */
  private List<Country> loadAllCountries() throws DBException {
    List<Continent> continents = loadContinent();
    List<Integer> continentIds = new ArrayList<>(continents.size());
    for (Continent continent : continents) {
      continentIds.add(continent.getId());
    }
    String ids = StringUtils.join(continentIds, ',');
    String sql = "select t.id,t.parentid,t.catename,t.catename_en,t.catename_py from qyer" +
      ".category as t WHERE t.parentid in (" + ids + ") and t.catename_py <> '' order by " +
      "catename_py asc";
    return loadCountries(sql);
  }

  /**
   * 根据指定sql, 获取城市列表
   *
   * @param sql
   * @return
   * @throws DBException
   */
  public List<City> loadCities(String sql) throws DBException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    List<City> cities = Lists.newArrayListWithCapacity(7);
    try {
      conn = tryConnect(qyerDBResourceManager.getDataSource(), 20, "loadEDMCampaign");
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        cities.add(
          createCityWithEnglishPY(rs.getInt("id"), rs.getInt("countryid"), rs.getString("cityname"),
                                  rs.getString("cityname_en"), rs.getString("cityname_py")));
      }
    } catch (Exception e) {
      throw new DBException(e);
    } finally {
      closeAllQuietly(conn, pstmt, rs);
    }
    return cities;
  }

  /**
   * 获取所有城市列表
   *
   * @return
   * @throws DBException
   */
  public List<City> loadAllCities() throws DBException {
    String sql = "SELECT t.id,t.countryid, t.cityname,t.cityname_en,t.cityname_py FROM qyer.city " +
      "AS t WHERE t.cityname IS NOT NULL AND t.cityname <> ''AND t.cityname_py <> '' ORDER BY " +
      "cityname_py ASC";
    return loadCities(sql);
  }

  /**
   * 构建国家字典
   *
   * @return
   * @throws DBException
   */
  public PlaceDict<Integer, Country> buildCountryDict() throws DBException {
    List<Country> allCountries = loadAllCountries();
    // 经过计算得出平均每个大洲有35个国家
    PlaceDict<Integer, Country> placeDict = new PlaceDict(35, 7);
    for (Country country : allCountries) {
      placeDict.putPlace(country.getParentId(), country);
    }
    return placeDict;
  }

  /**
   * 构建城市字典
   *
   * @return
   * @throws DBException
   */
  public PlaceDict<Integer, City> buildCityDict() throws DBException {
    List<City> allCities = loadAllCities();
    // 经过计算得出平均每个国家有38个城市, 共约250个国家
    PlaceDict<Integer, City> placeDict = new PlaceDict(35, 250);
    for (City city : allCities) {
      placeDict.putPlace(city.getParentId(), city);
    }
    return placeDict;
  }

  public List<Province> loadAllChinaProvinces() throws DBException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "SELECT * FROM ra_dictionary.china_province_city AS t WHERE t.parent_id = 11 " +
      "ORDER BY t" + "" +
      ".parent_id, t.name_py";
    List<Province> provinces = Lists.newArrayListWithCapacity(34);
    try {
      conn = tryConnect(dmdbResourceManager.getDataSource(), 20, "loadAllChinaProvinces");
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        provinces
          .add(createChinaProvince(rs.getInt("id"), rs.getInt("parent_id"), rs.getString("name")));
      }
    } catch (Exception e) {
      throw new DBException(e);
    } finally {
      closeAllQuietly(conn, pstmt, rs);
    }
    LOGGER.info("{} provinces of China have loaded to mem.", provinces.size());
    return provinces;
  }

  /**
   * 构建中国的省份-城市字典
   * <p/>
   * 中国的例子比较特殊, 因为在某些场景(如出发地模型), 使用者需要指定一个省份或某些主要城市, 也不需要过细致的城市(如县城).
   * <p/>
   * 目前(截至到2016年5月4日), qyer.city表里的中国的城市是混乱的, 包含了3000个左右的
   * <p/>
   * 中国的直辖市, 一线二线城市, 地级市, 县城, 根本无法区分从属关系. 几乎无法使用.
   * <p/>
   * 因此根据官方的数据, 构建了一套只有中国的省份直辖市+省会+主要城市(刨去地级市, 附属市, 县城)的纯洁的字典.
   * <p/>
   * 所有中国的数据都要从这份字典里加载.
   *
   * @return
   * @throws DBException
   */
  public PlaceDict<Integer, City> buildChinaCityDict() throws DBException {
    PlaceDict<Integer, City> placeDict = new PlaceDict(50, 50);
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = "SELECT * FROM ra_dictionary.china_province_city AS t WHERE t.parent_id <> 11";
    List<City> allChinaCities = Lists.newArrayListWithExpectedSize(400);
    try {
      conn = tryConnect(dmdbResourceManager.getDataSource(), 20, "buildChinaCityDict");
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        allChinaCities
          .add(createCity(rs.getInt("id"), rs.getInt("parent_id"), rs.getString("name")));
      }
    } catch (Exception e) {
      throw new DBException(e);
    } finally {
      closeAllQuietly(conn, pstmt, rs);
    }
    for (City chinaCity : allChinaCities) {
      placeDict.putPlace(chinaCity.getParentId(), chinaCity);
    }
    return placeDict;
  }

  public Map<Integer, Country> buildCountryInfoMap() throws DBException {
    List<Country> allCountries = loadAllCountries();
    Map<Integer, Country> countryMap = Maps.newHashMapWithExpectedSize(allCountries.size());
    for (Country country : allCountries) {
      countryMap.put(country.getId(), country);
    }
    return countryMap;
  }

  public Map<Integer, City> buildCityInfoMap() throws DBException {
    List<City> allCities = loadAllCities();
    Map<Integer, City> cityMap = Maps.newHashMapWithExpectedSize(allCities.size());
    for (City city : allCities) {
      cityMap.put(city.getId(), city);
    }
    return cityMap;
  }
}
